package com.example.mysqljdbc;

import java.sql.*;

public class JDBCExample {
    // 数据库连接参数
    private static final String URL = "jdbc:mysql://localhost:3306/testdb?useSSL=false&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";

    public static void main(String[] args) {
        // 测试数据库连接
        testConnection();

        // 创建表
        createTable();

        // 新增数据
        insertData();

        // 查询数据
        queryData();

        // 修改数据
        updateData();

        // 删除数据
        deleteData();
    }

    /**
     * 测试数据库连接
     */
    public static void testConnection() {
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            System.out.println("数据库连接成功！");
        } catch (SQLException e) {
            System.err.println("数据库连接失败：" + e.getMessage());
        }
    }

    /**
     * 创建用户表
     */
    public static void createTable() {
        String sql = "CREATE TABLE IF NOT EXISTS users (" +
            "id INT AUTO_INCREMENT PRIMARY KEY, " +
            "name VARCHAR(50) NOT NULL, " +
            "email VARCHAR(100), " +
            "age INT)";

        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
            System.out.println("用户表创建成功！");
        } catch (SQLException e) {
            System.err.println("创建表失败：" + e.getMessage());
        }
    }

    /**
     * 新增数据 - 使用Statement
     */
    public static void insertData() {
        String sql = "INSERT INTO users (name, email, age) VALUES " +
            "('张三', 'zhangsan@example.com', 25), " +
            "('李四', 'lisi@example.com', 30)";

        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             Statement stmt = conn.createStatement()) {
            int rows = stmt.executeUpdate(sql);
            System.out.println("新增数据成功，影响行数：" + rows);
        } catch (SQLException e) {
            System.err.println("新增数据失败：" + e.getMessage());
        }
    }

    /**
     * 查询数据
     */
    public static void queryData() {
        String sql = "SELECT * FROM users";

        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("=== 查询结果 ===");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                int age = rs.getInt("age");

                System.out.printf("ID: %d, 姓名: %s, 邮箱: %s, 年龄: %d%n",
                    id, name, email, age);
            }
        } catch (SQLException e) {
            System.err.println("查询数据失败：" + e.getMessage());
        }
    }

    /**
     * 修改数据 - 使用PreparedStatement（推荐）
     */
    public static void updateData() {
        String sql = "UPDATE users SET age = ? WHERE name = ?";

        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, 26);  // 设置年龄为26
            pstmt.setString(2, "张三"); // 设置姓名为张三

            int rows = pstmt.executeUpdate();
            System.out.println("修改数据成功，影响行数：" + rows);
        } catch (SQLException e) {
            System.err.println("修改数据失败：" + e.getMessage());
        }
    }

    /**
     * 删除数据
     */
    public static void deleteData() {
        String sql = "DELETE FROM users WHERE name = ?";

        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, "李四");
            int rows = pstmt.executeUpdate();
            System.out.println("删除数据成功，影响行数：" + rows);
        } catch (SQLException e) {
            System.err.println("删除数据失败：" + e.getMessage());
        }
    }

    /**
     * 使用PreparedStatement新增数据（安全推荐）
     */
    public static void insertWithPreparedStatement() {
        String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置参数
            pstmt.setString(1, "王五");
            pstmt.setString(2, "wangwu@example.com");
            pstmt.setInt(3, 28);

            int rows = pstmt.executeUpdate();
            System.out.println("使用PreparedStatement新增数据成功，影响行数：" + rows);
        } catch (SQLException e) {
            System.err.println("新增数据失败：" + e.getMessage());
        }
    }
}

